package seed.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import org.apache.commons.dbutils.handlers.ScalarHandler;


import seed.domain.Comment;
import seed.domain.Message;
import seed.domain.P_recommend;
import seed.domain.PageBean;
import seed.domain.Seed;
import user.domain.User;
import cn.itcast.commons.CommonUtils;
import cn.itcast.jdbc.TxQueryRunner;


public class SeedDao {
	 QueryRunner qr = new TxQueryRunner();
  /**
   * 鍒楀嚭鎵�鏈夊浘涔�
   * @return
   */
	public PageBean<Seed> findAll(int pc,int ps) {
		
		try {
			/*
			 * 1.鍒涘缓PageBean瀵硅薄pb
			 * 2.璁剧疆pb鐨刾c鍜宲s
			 * 3.寰楀埌tr锛岃缃粰pb
			 * 4.寰楀埌beanList锛岃缃粰pb
			 * 4.杩斿洖pb
			 */
			PageBean<Seed> pb = new PageBean<Seed>();
			pb.setPc(pc);
			pb.setPs(ps);
			//寰楀埌tr
			String sql = "SELECT COUNT(*) FROM seed";
			Number num = (Number)qr.query(sql, new ScalarHandler());
			int tr = num.intValue();
			pb.setTr(tr);
			//寰楀埌BeanList
			sql = "SELECT * FROM seed order by time desc LIMIT ?,? ";
			List<Seed> beanList = qr.query(sql, new BeanListHandler<Seed>(Seed.class),(pc-1)*ps,ps );
		    pb.setBeanList(beanList);
		    return pb;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	/**
	 * 鍔犺浇璇︾粏椤�
	 * 
	 * @param sid
	 * @return
	 */
   
	public Seed load(String sid) {
		try {
			
			String sql = "select * from seed where sid=?";
			return qr.query(sql, new BeanHandler<Seed>(Seed.class), sid);
			
			
		} catch(SQLException e) {
			return null;
		}
	}
	/**
	 * 鎸夊垎绫绘煡璇�
	 * @param cid
	 * @return
	 */
	public PageBean<Seed> findByCategory(String cid,int pc,int ps) {
		try {

			PageBean<Seed> pb = new PageBean<Seed>();
			pb.setPc(pc);
			pb.setPs(ps);
			//寰楀埌tr
			String sql = "SELECT COUNT(*) FROM seed  where cid="+ cid;
			Number num = (Number)qr.query(sql, new ScalarHandler());
			int tr = num.intValue();
			pb.setTr(tr);
			//寰楀埌BeanList
			sql = "SELECT * FROM seed where cid=? LIMIT ?,?";
			List<Seed> beanList = qr.query(sql, new BeanListHandler<Seed>(Seed.class),cid,(pc-1)*ps,ps );
		    pb.setBeanList(beanList);
			
			return pb;
		} catch(SQLException e) {
		
			return null;
		}
		
	}
	
	
	public Seed getSeedBySid(String sid) {
try {
			
			String sql = "select * from seed where sid=?";
			return qr.query(sql, new BeanHandler<Seed>(Seed.class), sid);
			
		} catch(SQLException e) {
			return null;
		}
	}
	
	
	/**
	 *message 
	 * @throws SQLException 
	 */
	public List<Message> listMessage() throws SQLException
	{
		
		return  qr.query("select * from message", new BeanListHandler<Message>(Message.class));
	}
	
	public User getUserByUid(String uid)
	{
		
		try {
			String sql =  "SELECT * FROM user WHERE uid=?";
			return qr.query(sql, new BeanHandler<User>(User.class),uid);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}
	
	public boolean addMessage(Message message)
	{
		try {
			String sql = "insert into message(messageId,uid,content,time) values(?,?,?,?)";
			Object[] parms = {message.getMessageId(),message.getUid(),message.getContent(),message.getTime()};
			qr.update(sql,parms);
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		
	}
	public List<Seed> query(Seed creatia) {
		try {
			/*
			 * 1. 给出sql模板
			 * 2. 给出参数
			 * 3. 调用query方法，使用结果集处理器：BeanListHandler
			 */
			/*
			 * 一、　给出sql模板
			 * 二、　给出参数！
			 */
			/*
			 * 1. 给出一个sql语句前半部
			 */
			String sql = "select * from seed where sname like '%"+creatia.getSname()+"%'";
		
			return qr.query(sql,new BeanListHandler(Seed.class));
		} catch(SQLException e) {
			return null;
		}
	}
	
	
	/**
	 * 添加评论方法
	 * @param comment
	 * @return
	 */
	public boolean addComment(Comment comment) {
		try {
			String sql = "insert into comment(commentId,uid,content,time,sid) values(?,?,?,?,?)";
			Object[] parms = {comment.getCommentId(),comment.getUid(),comment.getContent(),comment.getTime(),comment.getSid()};
			qr.update(sql,parms);
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}
	
	public List<Comment> commentMessage(String sid) {
		try {
			return  qr.query("select * from comment where sid = '" +sid+"'", new BeanListHandler<Comment>(Comment.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
/////////////////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////
/*
* 
* 
* 
* 
* 
* 
* 
* 
* 后台方法
* 
* 
* 
* 
* 
*/
	public Object add(Seed seed) {
		System.out.println(seed);
		try {
			String sql = "INSERT INTO seed(sid,sname,price,large,image,xiangxi,yaodian,cid,time) VALUES(?,?,?,?,?,?,?,?,?)";
			Object[] params = {seed.getSid(),seed.getSname(),seed.getPrice(),seed.getLarge(),seed.getImage(),seed.getXiangxi(),seed.getYaodian(),seed.getCategory().getCid(),seed.getTime()};
			qr.update(sql, params);
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		
		return null;
	}
	/**
	 * 删除种子
	 * @param sid
	 */
	public void delete(String sid) {
		try {
			String sql = "update seed set del=true where sid=?";
			qr.update(sql, sid);
		} catch(SQLException e) {
			throw new RuntimeException(e);
		}
	}
	/**
	 * 查询指定分类下的图书本数
	 * @param cid
	 * @return
	 */
	public int getCountByCid(String cid) {
		try {
			String sql = "select count(*) from seed where cid=?";
			Number cnt = (Number)qr.query(sql, new ScalarHandler(), cid);
			return cnt.intValue();
		} catch(SQLException e) {
			throw new RuntimeException(e);
		}
	}
	//后台删除留言方法
	public void messageDelete(String  messageId) {
		try {
			String sql = "delete from message where  messageId=?";
			qr.update(sql,  messageId);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		
	}
	public void updateOverNumber(String sid){
		try {
			String sql = "update seed set overNumber=(overNumber + 1) where sid=?";
			qr.update(sql, sid);
		} catch(SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public void addPersonalRecommend(String uid,String sid) {
	
		try {
			String sql = "INSERT INTO p_recommend(prid,uid,sid) VALUES(?,?,?)";
			Object[] params = {CommonUtils.uuid(),uid,sid};
			qr.update(sql, params);
		} catch (SQLException e) {
			return;
		}
	}
	public void updatePersonalRecommendNumber(String prid){
		try {
			String sql = "update p_recommend set number=(number + 1) where prid=?";
			qr.update(sql, prid);
		} catch(SQLException e) {
			throw new RuntimeException(e);
		}
	}
	public P_recommend isPersonalRecommend(String uid,String sid){
		try {			 
			 Object[] param = {uid,sid};
			 P_recommend  p =  qr.query("select * from p_recommend where uid= ? and sid = ?",new BeanHandler<P_recommend>(P_recommend.class),param);
			 return p;
		} catch(SQLException e) {			
			return null;
		}
	}
	public  List<P_recommend> getPersonalRecommend(String uid){
		System.out.println(uid);
		try {
			return  qr.query("select * from p_recommend where uid =  '" + uid +"'   order by number desc limit 0,5",
					new BeanListHandler<P_recommend>(P_recommend.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	} 

	
}

	
